package drive.Mapper;

import drive.Model.TransactionOrder;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface TransactionOrderMapper {
    // 查询所有交易订单
    @Select("SELECT * FROM transactionorder")
    List<TransactionOrder> findAll();

    // 根据orderID查询交易订单
    @Select("SELECT * FROM transactionorder WHERE orderID = #{orderID}")
    TransactionOrder findByOrderID(int orderID);

    // 根据buyerID查询交易订单
    @Select("SELECT * FROM transactionorder WHERE buyerID = #{buyerID}")
    List<TransactionOrder> findByBuyerID(int buyerID);

    // 根据sellerID查询交易订单
    @Select("SELECT * FROM transactionorder WHERE sellerID = #{sellerID}")
    List<TransactionOrder> findBySellerID(int sellerID);

    // 根据itemID查询交易订单
    @Select("SELECT * FROM transactionorder WHERE itemID = #{itemID}")
    TransactionOrder findByItemID(int itemID);

    // 新增交易订单
    @Insert("INSERT INTO transactionorder (buyerID, sellerID, itemID, buyerReview, sellerReview, buyerRating, sellerRating, creationTime, status, addrID) VALUES (#{buyerID}, #{sellerID}, #{itemID}, #{buyerReview}, #{sellerReview}, #{buyerRating}, #{sellerRating}, #{creationTime}, #{status}, #{addrID})")
    int add(TransactionOrder order);

    // 更新交易订单
    @Update("UPDATE transactionorder SET buyerID = #{buyerID}, sellerID = #{sellerID}, itemID = #{itemID}, buyerReview = #{buyerReview}, sellerReview = #{sellerReview}, buyerRating = #{buyerRating}, sellerRating = #{sellerRating}, status = #{status}, addrID = #{addrID} WHERE orderID = #{orderID}")
    int update(TransactionOrder order);

    // 删除交易订单
    @Delete("DELETE FROM transactionorder WHERE orderID = #{orderID}")
    int delete(int orderID);

    /**
     * 按月统计所有用户的订单总金额和订单数量
     * @return 每月统计信息列表
     */
    @Select("SELECT DATE_FORMAT(creationTime, '%Y-%m') AS month, COUNT(*) AS orderCount, SUM(price) AS totalAmount FROM transactionorder GROUP BY DATE_FORMAT(creationTime, '%Y-%m') ORDER BY month")
    List<Map<String, Object>> getMonthlyTransactionStats();
}